﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using MySql.Data.MySqlClient;

namespace MakeIT.MiraPark.Negocios
{
    public static partial class ModelBO
    {

        public static List<ModeloVO> getModelos()
        {
            return getModelos(false, true, null, null);
        }
        public static ModeloVO getModelo(int idModelo)
        {
            List<ModeloVO> listaModeloVO = getModelos(false, false, null, idModelo);
            if (listaModeloVO != null && listaModeloVO.Count > 0)
                return listaModeloVO[0];
            else
                return null;
        }
        public static List<ModeloVO> getModelos(MarcaVO marcaVO)
        {
            return getModelos(false, true, marcaVO, null);
        }
        public static List<ModeloVO> getModelos(bool orderbyMarca, bool orderbyModelo)
        {
            return getModelos(orderbyMarca, orderbyModelo, null, null);
        }
        public static List<ModeloVO> getModelos(bool orderbyMarca, bool orderbyModelo, MarcaVO marcaVO, int? idModelo)
        {
            return getModelos(orderbyMarca, orderbyModelo, marcaVO, idModelo, null, false);
        }
        public static List<ModeloVO> getModelos(bool orderbyMarca, bool orderbyModelo, MarcaVO marcaVO, int? idModelo, TipoVeiculoVO tipoVeiculoVO, bool permitido)
        {
            StringBuilder query = new StringBuilder();
            string order = "ORDER BY ";
            string where = "WHERE ";

            query.AppendLine("SELECT m.IdModelo IdModelo, m.IdTipoVeiculo IdTipoVeiculo, tv.TipoVeiculo TipoVeiculo, m.IdMarca, mc.Marca Marca, m.IdPorteVeiculo, pv.PorteVeiculo PorteVeiculo, m.Modelo Modelo, m.Obs Obs, m.Ordem ordem ");
            query.AppendLine("FROM modelo m LEFT JOIN tipoveiculo tv on m.IdTipoVeiculo = tv.IdTipoVeiculo LEFT JOIN marca mc on m.IdMarca = mc.IdMarca LEFT JOIN porteveiculo pv on m.IdPorteVeiculo = pv.IdPorteVeiculo ");
            if (marcaVO != null)
            {
                query.AppendLine(where + "m.IdMarca=?IdMarca ");
                where = "AND ";
            }
            if (idModelo != null)
            {
                query.AppendLine(where + "m.IdModelo=?IdModelo ");
                where = "AND ";
            }
            if (tipoVeiculoVO != null)
            {
                query.AppendLine(where + "m.IdTipoVeiculo=?IdTipoVeiculo ");
                where = "AND ";
            }
            if (permitido)
            {
                query.AppendLine(where + "tv.Permitido ");
                where = "AND ";
            }
            if (orderbyMarca)
            {
                query.AppendLine(order + "mc.Marca ");
                order = ", ";
            }
             if (orderbyModelo)
             {
                 query.AppendLine(order + "m.Ordem, m.Modelo ");
                 order = ", ";
             }
             
            
            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (marcaVO != null) cmd.Parameters.AddWithValue("?IdMarca", marcaVO.idMarca);
            if (idModelo != null) cmd.Parameters.AddWithValue("?IdModelo", idModelo);
            if (tipoVeiculoVO != null) cmd.Parameters.AddWithValue("?IdTipoVeiculo", tipoVeiculoVO.idTipoVeiculo);

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<ModeloVO> listModelo = new List<ModeloVO>();

                while (reader.Read())
                {
                    ModeloVO modeloVO = ModeloVO.CreateModeloVO();
                    modeloVO.idModelo = reader.GetInt32("IdModelo");
                    modeloVO.idTipoVeiculo = reader.GetInt32("IdTipoVeiculo");
                    modeloVO.tipoVeiculoVO.tipoVeiculo = reader.GetString("TipoVeiculo");
                    modeloVO.idMarca = reader.GetInt32("idMarca");
                    modeloVO.marcaVO.marca = reader.GetString("Marca");
                    modeloVO.idPorteVeiculo = reader.GetInt32("idPorteVeiculo");
                    modeloVO.porteVeiculoVO.porteVeiculo = reader.GetString("PorteVeiculo");
                    modeloVO.modelo = reader.GetString("Modelo");
                    modeloVO.obs = reader.GetString("Obs");
                    modeloVO.ordem = reader.GetInt32("Ordem");

                    listModelo.Add(modeloVO);
                }
                reader.Close();
                return listModelo;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setModelo(ModeloVO modeloVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE modelo ");
            query.AppendLine("SET Modelo=?Modelo, IdTipoVeiculo=?IdTipoVeiculo, idMarca=?idMarca, idPorteVeiculo=?idPorteVeiculo, Obs=?Obs, Ordem=?Ordem ");
            query.AppendLine("WHERE IdModelo=?IdModelo ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdModelo", modeloVO.idModelo);
            cmd.Parameters.AddWithValue("?IdTipoVeiculo", modeloVO.idTipoVeiculo);
            cmd.Parameters.AddWithValue("?idMarca", modeloVO.idMarca);
            cmd.Parameters.AddWithValue("?idPorteVeiculo", modeloVO.idPorteVeiculo);
            cmd.Parameters.AddWithValue("?Modelo", modeloVO.modelo);
            cmd.Parameters.AddWithValue("?Obs", modeloVO.obs);
            cmd.Parameters.AddWithValue("?Ordem", modeloVO.ordem);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newModelo(ModeloVO modeloVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO modelo ");
            query.AppendLine("SET Modelo=?Modelo, IdTipoVeiculo=?IdTipoVeiculo, idMarca=?idMarca, idPorteVeiculo=?idPorteVeiculo, Obs=?Obs, Ordem=?Ordem ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?Modelo", modeloVO.modelo);
            cmd.Parameters.AddWithValue("?Obs", modeloVO.obs);
            cmd.Parameters.AddWithValue("?IdTipoVeiculo", modeloVO.idTipoVeiculo);
            cmd.Parameters.AddWithValue("?idMarca", modeloVO.idMarca);
            cmd.Parameters.AddWithValue("?idPorteVeiculo", modeloVO.idPorteVeiculo);
            cmd.Parameters.AddWithValue("?Ordem", modeloVO.ordem);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delModelo(ModeloVO modeloVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM modelo ");
            query.AppendLine("WHERE IdModelo=?IdModelo ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdModelo", modeloVO.idModelo);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static bool readyToDeleteModelo(int idModelo)
        {
            int contagem = 0;

            MySqlDataReader reader = null;
            StringBuilder query = new StringBuilder();
            MySqlCommand cmd = getConnection();
            cmd.Connection.Open();

            try
            {

                query = new StringBuilder();
                query.AppendLine("SELECT count(*) contagem FROM veiculo WHERE IdModelo=?IdModelo ");
                cmd.CommandText = query.ToString();
                cmd.Parameters.AddWithValue("?IdModelo", idModelo);
                reader = cmd.ExecuteReader();
                reader.Read();
                contagem = reader.GetInt32("contagem");
                reader.Close();

            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

            return contagem == 0;

        }
    
    }
}
